package org.example.ims.Repository.WarehouseProduct;

import org.example.ims.DTO.InventoryItemDTO;
import org.example.ims.DTO.ProductWithInventoryDTO;
import org.example.ims.Entity.Base.Product;
import org.example.ims.Entity.Base.Warehouse;
import org.example.ims.Entity.WareHouseProduct.WarehouseProduct;
import org.example.ims.Entity.WareHouseProduct.WarehouseProductId;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Optional;

@Repository
public interface WarehouseProductRepository extends JpaRepository<WarehouseProduct, WarehouseProductId>, JpaSpecificationExecutor<WarehouseProduct> {

    Optional<WarehouseProduct> findByWarehouseAndProduct(Warehouse warehouse, Product product);

    @Query("SELECT new org.example.ims.DTO.ProductWithInventoryDTO(wp.product, wp.quantity) " +
            "FROM WarehouseProduct wp " +
            "WHERE (:warehouseId IS NULL OR wp.warehouse.warehouseId = :warehouseId) " +
            "AND (:status IS NULL OR wp.product.productStatus = :status)" +
            "AND wp.quantity > 0")
    Page<ProductWithInventoryDTO> findProductsWithInventoryByConditions(
            @Param("warehouseId") Integer warehouseId,
            @Param("status") String status,
            Pageable pageable
    );

    @Query("SELECT new org.example.ims.DTO.InventoryItemDTO(wp.product, wp.quantity, wh.warehouseName, wh.stockWarningLevel, cat.categoryName) " +
            "FROM WarehouseProduct wp " +
            "JOIN wp.warehouse wh " +
            "JOIN wp.product p " +
            "LEFT JOIN p.category cat " +
            "WHERE (:warehouseId IS NULL OR wp.warehouse.warehouseId = :warehouseId) " +
            "AND (:categoryId IS NULL OR cat.categoryId = :categoryId) " +
            "AND (:productStatus IS NULL OR p.productStatus = :productStatus) " +
            "AND (:productName IS NULL OR p.productName LIKE %:productName%) " +
            "AND (:minStock IS NULL OR wp.quantity >= :minStock) " +
            "AND (:maxStock IS NULL OR wp.quantity <= :maxStock)")
    Page<InventoryItemDTO> findProductsWithInventoryByConditions(
            @Param("warehouseId") Integer warehouseId,
            @Param("categoryId") Integer categoryId,
            @Param("productStatus") String productStatus,
            @Param("productName") String productName,
            @Param("minStock") Integer minStock,
            @Param("maxStock") Integer maxStock,
            Pageable pageable);
}